create database examDB
go
use examDB
go
create table tblTruongHoc(
[stt] int identity(1,1) primary key,
[truong] nvarchar(max) not null,
[diadiem] nvarchar(max) not null,
[dienthoai] nvarchar(255),
[fax] nvarchar(255),
[email] nvarchar(255),
[user] nvarchar(255) not null,
[pass] nvarchar(255) not null,
)
go
create table tblGiangVien(
[stt] int identity(1,1) primary key,
[hoten] nvarchar(255),
[ngaysinh] datetime,
[diachi] nvarchar(255),
[dienthoai] nvarchar(255),
[email] nvarchar(255),
[gioitinh] bit default 1,
[pass] nvarchar(255),
)
go
create table tblSinhVien(
[stt] int identity(1,1) primary key,
[hoten] nvarchar(255),
[ngaysinh] datetime,
[diachi] nvarchar(255),
[dienthoai] nvarchar(255),
[email] nvarchar(255),
[gioitinh] bit default 1,
[pass] nvarchar(255),
)
go
create table tblKhoa(
[stt] int identity(1,1) primary key,
[idTruong] int Foreign key References tblTruongHoc(stt),
[tenkhoa] nvarchar(255),
[mota] nvarchar(max),
)
go
create table tblCTDT(
[stt] int identity(1,1) primary key,
[chuongtrinh] nvarchar(255),
[mota] nvarchar(255),
[niemkhoa] nvarchar(255),
)
go
create table tblKhoaHoc(
[stt] int identity(1,1) primary key,
[idKhoa] int foreign key references tblKhoa(stt),
[idCTDT] int foreign key references tblCTDT(stt),
[thoigiandaotao] nvarchar(255),
[ngayStart] datetime,
[dateEnd] datetime,
[isEnd] bit default 1,
[tongTC] int default 0,
)
go
create table tblLopHoc(
[stt] int identity(1,1) primary key,
[idKhoa] int foreign key references tblKhoa(stt),
[idCTDT] int foreign key references tblCTDT(stt),
[ten] nvarchar(255),
[ngaydangki] datetime,
[ngaykhaigiang] datetime,
[ngayketthuc] datetime,
[isEnd] bit default 1,
)
go
create table tblDangKyLop(
[stt] int identity(1,1) primary key,
[idLH] int foreign key references tblLopHoc(stt),
[idSV] int foreign key references tblSinhVien(stt),
[ngaydangky] datetime,
)
go
create table tblMonHoc(
[stt] int identity(1,1) primary key,
[monhoc] nvarchar(255),
[soTC] nvarchar(255),
)
go
create table tblKH_MH(
[stt] int identity(1,1) primary key,
[idHK] int foreign key references tblKhoaHoc(stt),
[idMH] int foreign key references tblMonHoc(stt),
[soTC] nvarchar(255),
)
go
create table tblThuVienHinhAnh(
[stt] int identity(1,1) primary key,
[img] varbinary(max) null,
)
go
create table tblLoaiCauHoi(
[stt] int identity(1,1) primary key,
[ten] nvarchar(255),
)
go
create table tblNganHangCauHoi(
[stt] int identity(1,1) primary key,
[idMH] int foreign key references tblMonHoc(stt),
[idLoaiCauHoi] int foreign key references tblLoaiCauHoi(stt), 
[idIMG] int foreign key references tblThuVienHinhAnh(stt),
[noidung] nvarchar(max),
[cauTraLoi] nvarchar(max),
)
go
create table tblDapAn(
[stt] int identity(1,1) primary key,
[idNHCH] int foreign key references tblNganHangCauHoi(stt),
[noidung] nvarchar(max),
)
go
create table tblDeThi(
[stt] int identity(1,1) primary key,
[idHM] int foreign key references tblMonHoc(stt),
[idGV] int foreign key references tblGiangVien(stt),
[dethi] nvarchar(255),
[thoigianlambai] nvarchar(255),
[soluongcauhoi] int,
[diemdat] nvarchar(255),
[diemtoida] nvarchar(255),
[ghichu] nvarchar(255),
[ghichusaukhithi] nvarchar(255),
[ngaytao] datetime default GETDATE(),
)
go
create table tblCauHoiThi(
[stt] int identity(1,1) primary key,
[idDT] int foreign key references tblDeThi(stt),
[idCH] int foreign key references tblNganHangCauHoi(stt),
)
go
create table tblKyThi(
[stt] int identity(1,1) primary key,
[idLH] int foreign key references tblLopHoc(stt),
[idHM] int foreign key references tblMonHoc(stt),
[idDT] int foreign key references tblDeThi(stt),
[ten] nvarchar(255),
[ngaythi] datetime,
[giothi] nvarchar(255),
[giamkhao1] int,
[giamkhao2] int,
[giaovienchamthi] int,
)
go
create table tblKetQuaThi(
[stt] int identity(1,1) primary key,
[idDT] int foreign key references tblDeThi(stt),
[idSV] int foreign key references tblSinhVien(stt),
[diemthi] nvarchar(255),
[ketqua] bit default 0,
[isPhucKhao] bit default 0,
)
go
create table tblBaiThi(
[stt] int identity(1,1) primary key,
[idKQT] int foreign key references tblKetQuaThi(stt), 
[idCHT] int foreign key references tblCauHoiThi(stt), 
[dapancuoicung] nvarchar(255),
[noidung] nvarchar(255),
)